using System;
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Collections.Generic;
using Colorprj.Masters;
using Colorprj.Common; 
namespace Colorprj.Purchase
{
    class PurchaseManager
    {

        /// <summary>
        /// Transfers an amount between two accounts.
        /// </summary>
        /// <param name="transactionAmount">Amount to transfer.</param>
        /// <param name="sourceAccount">Account to be credited.</param>
        /// <param name="destinationAccount">Account to be debited.</param>
        /// <returns>true if sucessful; otherwise false.</returns>
        /// <remarks>Demonstrates executing multiple updates within the 
        /// context of a transaction.</remarks>
        public bool AddPurchase(PurchaseHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "AddPurHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "PurNo", DbType.String, phEntity.PurNo );
            db.AddInParameter(creditCommand, "PurDate", DbType.DateTime, phEntity.PurDate );
            db.AddInParameter(creditCommand, "PartyId", DbType.Int32, phEntity.PartyId);
            db.AddInParameter(creditCommand, "CenVat", DbType.Double , phEntity.CenVat );
            db.AddInParameter(creditCommand, "Ecess", DbType.Double, phEntity.Ecess );
            db.AddInParameter(creditCommand, "SHEcess", DbType.Double, phEntity.SHEcess );
            db.AddInParameter(creditCommand, "Total", DbType.Double, phEntity.Total );
            db.AddInParameter(creditCommand, "STotal", DbType.Double, phEntity.STotal );
            db.AddInParameter(creditCommand, "Gtotal", DbType.Double, phEntity.GTotal );
            db.AddInParameter(creditCommand, "CST", DbType.Double, phEntity.CST );
            db.AddInParameter(creditCommand, "Scharge", DbType.Double, phEntity.Scharge );
            db.AddInParameter(creditCommand, "Freight", DbType.Double, phEntity.Freight );


            List<PurDetailEntity> PurDetailList = phEntity.PurDetailList;

           
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int   purHeadId=Convert.ToInt16(db.ExecuteScalar (creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddPurDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (PurDetailEntity pur in PurDetailList)
                    {
                        DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                        db.AddInParameter(debitCommand, "PurHeadId", DbType.Int32, purHeadId);
                        //db.AddInParameter(debitCommand, "ProductId", DbType.Int32, pur.ProductId);
                        db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, pur.SubproductId);
                        db.AddInParameter(debitCommand, "Qty", DbType.Decimal, pur.Qty);
                        db.AddInParameter(debitCommand, "Rate", DbType.Decimal , pur.Rate);
                        db.AddInParameter(debitCommand, "Amount", DbType.Decimal , pur.Amount);

                        db.ExecuteNonQuery(debitCommand, transaction);
                        
                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception ex)
                {
                    // Rollback transaction
                    
                    transaction.Rollback();

                    throw new Exception(ex.Message) ; 
                }
                connection.Close();

                return result;
            }
        }


        /// <summary>
        /// Transfers an amount between two accounts.
        /// </summary>
        /// <param name="transactionAmount">Amount to transfer.</param>
        /// <param name="sourceAccount">Account to be credited.</param>
        /// <param name="destinationAccount">Account to be debited.</param>
        /// <returns>true if sucessful; otherwise false.</returns>
        /// <remarks>Demonstrates executing multiple updates within the 
        /// context of a transaction.</remarks>
        public bool UpdPurchase(PurchaseHeadEntity phEntity)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "UpdPurHead";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "PurNo", DbType.String, phEntity.PurNo);
            db.AddInParameter(creditCommand, "PurDate", DbType.DateTime, phEntity.PurDate);
            db.AddInParameter(creditCommand, "PartyId", DbType.Int32, phEntity.PartyId);
            db.AddInParameter(creditCommand, "PurHeadId", DbType.Int32, phEntity.PurHeadId);
            db.AddInParameter(creditCommand, "CenVat", DbType.Double, phEntity.CenVat);
            db.AddInParameter(creditCommand, "Ecess", DbType.Double, phEntity.Ecess);
            db.AddInParameter(creditCommand, "SHEcess", DbType.Double, phEntity.SHEcess);
            db.AddInParameter(creditCommand, "Total", DbType.Double, phEntity.Total);
            db.AddInParameter(creditCommand, "STotal", DbType.Double, phEntity.STotal);
            db.AddInParameter(creditCommand, "Gtotal", DbType.Double, phEntity.GTotal);
            db.AddInParameter(creditCommand, "CST", DbType.Double, phEntity.CST);
            db.AddInParameter(creditCommand, "Scharge", DbType.Double, phEntity.Scharge);
            db.AddInParameter(creditCommand, "Freight", DbType.Double, phEntity.Freight);


            List<PurDetailEntity> PurDetailList = phEntity.PurDetailList;


            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    int purHeadId = Convert.ToInt16(db.ExecuteScalar(creditCommand, transaction));
                    // Debit the second account
                    //db.ExecuteNonQuery(debitCommand, transaction);
                    sqlCommand = "AddPurDetail";
                    //DbCommand debitCommand = db.GetStoredProcCommand(sqlCommand);
                    foreach (PurDetailEntity pur in PurDetailList)
                    {
                        DbCommand debitCommand;
                        if (pur.PurDetailId == 0)
                        {
                             debitCommand = db.GetStoredProcCommand(sqlCommand);
                        }
                        else {
                            if (pur.Qty == 0) { debitCommand = db.GetStoredProcCommand("DelPurDetail"); }

                           else
                            {
                                debitCommand = db.GetStoredProcCommand("UpdPurDetail");
                                db.AddInParameter(debitCommand, "OldQty", DbType.Int32, pur.OldQty);
                            }
                            db.AddInParameter(debitCommand, "PurDetailId", DbType.Decimal, pur.PurDetailId);
                        }
                        if (pur.Qty != 0)
                        {
                            db.AddInParameter(debitCommand, "PurHeadId", DbType.Int32, phEntity.PurHeadId);
                            //db.AddInParameter(debitCommand, "ProductId", DbType.Int32, pur.ProductId);
                            db.AddInParameter(debitCommand, "SubproductId", DbType.Int32, pur.SubproductId);
                            db.AddInParameter(debitCommand, "Qty", DbType.Decimal, pur.Qty);
                            
                            db.AddInParameter(debitCommand, "Rate", DbType.Decimal, pur.Rate);
                            db.AddInParameter(debitCommand, "Amount", DbType.Decimal, pur.Amount);
                        }
                        db.ExecuteNonQuery(debitCommand, transaction);

                    }
                    // Commit the transaction
                    transaction.Commit();

                    result = true;
                }
                catch (Exception e)
                {
                    // Rollback transaction
                    transaction.Rollback();
                    throw new Exception(e.Message) ; 
                    

                }
                connection.Close();

                return result;
            }
        }


        public PurchaseHeadEntity GetPurchase(int PurId)
        {
            bool result = false;

            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelPurHeadDet";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "PurHeadId", DbType.Int32, PurId);

            DataSet PurchaseDataSet = db.ExecuteDataSet(creditCommand);
            PurchaseHeadEntity phEntity = new PurchaseHeadEntity(); 
            List<PurDetailEntity> PurDetailList =new  List<PurDetailEntity>();
            foreach (DataRow dr  in PurchaseDataSet.Tables[0].Rows)
            {
                phEntity.PartyId = Convert.ToInt16(dr["PartyId"]);
                phEntity.PurHeadId = Convert.ToInt16(dr["PurHeadId"]);
                phEntity.PurNo = dr["PurNo"].ToString() ;
                phEntity.PurDate = Convert.ToDateTime(dr["PurDate"]);

                phEntity.CenVat = Utility.getDblVal(dr["CenVat"]);
                phEntity.CST = Utility.getDblVal(dr["CST"]);
                phEntity.Ecess = Utility.getDblVal(dr["Ecess"]);
                phEntity.Freight = Utility.getDblVal(dr["Freight"]);
                phEntity.GTotal = Utility.getDblVal(dr["GTotal"]);
                phEntity.STotal = Utility.getDblVal(dr["STotal"]);
                phEntity.Total = Utility.getDblVal(dr["Total"]);
                phEntity.Scharge = Utility.getDblVal(dr["Scharge"]);
                phEntity.SHEcess = Utility.getDblVal(dr["SHEcess"]);


                //phEntity. = Convert.ToInt16(dr["PartyId"]);

                PurDetailEntity pdEntity = new PurDetailEntity();
                //pdEntity.ProductId = Convert.ToInt16(dr["ProductId"]); 
                pdEntity.SubproductId = Convert.ToInt16(dr["SubproductId"]);
                pdEntity.Qty = Convert.ToDouble(dr["Qty"]);
                pdEntity.Rate  = Convert.ToDouble  (dr["Rate"]);
                pdEntity.Amount = Convert.ToDouble(dr["Amount"]);
                pdEntity.PurDetailId = Convert.ToInt16(dr["PurDetailId"]);
                PurDetailList.Add(pdEntity); 
            }
            phEntity.PurDetailList =PurDetailList;
            return phEntity;
        }


      


        public SubProduct GetSubProductById(int Id)
        { 
        SubProductManager spm = new SubProductManager();

        return spm.GetSubProductById(Id); 
        }

    }
}
